/* NOTICE
    OpenJaWS - Open Java Weather Station
    
    Copyright (C) 2008 Grant Gardner <grant@lastweekend.com.au>
    
    OpenJaWS is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
    
NOTICE */

package au.com.lastweekend.openjaws.plugins;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.DatabaseMetaDataCallback;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.MetaDataAccessException;
import org.springframework.transaction.annotation.Transactional;

import au.com.lastweekend.openjaws.api.Readings;
import au.com.lastweekend.openjaws.api.WeatherPluginException;

/**
 * TODO: We store the station values in the table, but not the units. If the units change the existing rows should all get updated
 * to the new unit types. Could store a stationID with each row.
 * 
 * @author ggardner
 * 
 */
public class SQLPlugin extends AbstractWeatherPlugin {

    private static final Logger LOG = LoggerFactory.getLogger(SQLPlugin.class);

    private JdbcTemplate jdbc;

    static final String READINGS_TABLE = "readings";
    static final String READING_TIME_COLUMN = "reading_time";
    static final String CURRENT_COLUMN = "current";
    private static final String[] COLUMN_NAMES = {"indoor_temperature", "outdoor_temperature", "indoor_humidity",
            "outdoor_humidity", "wind_speed", "wind_direction", "rain_increment", "air_pressure"};

    private static final String INSERT_SQL = generateInsertSQL();
    private static final String UPDATE_SQL = generateUpdateSQL();
    private static final String READING_TIME_QUERY = generateReadingTimeQuery();

    private static String generateInsertSQL() {

        StringBuilder columnNames = new StringBuilder(String.format("INSERT INTO %s (%s,%s", READINGS_TABLE, READING_TIME_COLUMN,
                CURRENT_COLUMN));
        StringBuilder values = new StringBuilder(" VALUES (?,true");

        for (String columnName : COLUMN_NAMES) {
            columnNames.append(',');
            columnNames.append(columnName);
            values.append(",?");

        }
        columnNames.append(')');
        values.append(')');
        columnNames.append(values);
        return columnNames.toString();
    }

    private static String generateUpdateSQL() {

        return String.format("UPDATE %s SET %s = false WHERE %2$s = true", READINGS_TABLE, CURRENT_COLUMN);
    }

    private static String generateReadingTimeQuery() {

        return String.format("SELECT %s FROM %s WHERE %s = true", READING_TIME_COLUMN, READINGS_TABLE, CURRENT_COLUMN);
    }

    private DataSource dataSource;

    public SQLPlugin(DataSource dataSource) {

        this.dataSource = dataSource;
    }

    @PostConstruct
    public void init() throws WeatherPluginException {

        if (isEnabled()) {
            this.jdbc = new JdbcTemplate(dataSource);
            updateDatabase();
            LOG.info("Enabled");
        } else {
            LOG.info("Disabled");
        }
    }

    @Override
    protected Date getLastReadingTime() {

        Date lastReadingTime = null;

        ResultSetExtractor rse = new ResultSetExtractor() {

            public Object extractData(ResultSet rs) throws SQLException {

                if (rs.next()) {
                    return new Date(rs.getTimestamp(1).getTime());
                } else {
                    return null;
                }

            }

        };

        lastReadingTime = (Date) jdbc.query(READING_TIME_QUERY, new Object[]{}, rse);
        LOG.info(String.format("Initialised last known reading %tc", lastReadingTime));
        return lastReadingTime;

    }

    @Transactional(readOnly = false)
    public void processReadings(Readings readings) throws WeatherPluginException {

        if (readings.isDirty()) {
            // TODO For first reading. Really should try and "fix" the rain since last reading value. (eg hourly historic data.
            // OpenJaWS stops on the half hour in the middle of a storm)
            updateOldRow();
            insertNewRow(readings);
            if (LOG.isDebugEnabled()) {
                LOG.debug(String.format("Successful update for %tc", readings.getReadingTime()));
            }
        } else {
            LOG.debug("Skipping unchanged reading");
        }
    }

    private void insertNewRow(final Readings readings) {

        Double indoorTemperature = readings.getIndoorTemperature().getCurrent().getValue();
        Double outdoorTemperature = readings.getOutdoorTemperature().getCurrent().getValue();
        Double indoorHumidity = readings.getIndoorHumidity().getCurrent().getValue();
        Double outdoorHumidity = readings.getOutdoorHumidity().getCurrent().getValue();
        Double windSpeed = readings.getWindSpeed().getCurrent().getValue();
        Double windDirection = readings.getWindSpeed().getCurrent().getDirection();
        Double rainIncrement = readings.getRainIncrement().getValue();
        Double airPressure = readings.getAirPressure().getCurrent().getValue();

        Object[] args = new Object[]{new Timestamp(readings.getReadingTime().getTime()), indoorTemperature, outdoorTemperature,
                indoorHumidity, outdoorHumidity, windSpeed, windDirection, rainIncrement, airPressure};

        jdbc.update(INSERT_SQL, args);

    }

    private void updateOldRow() {

        jdbc.execute(UPDATE_SQL);
    }

    private void updateDatabase() throws WeatherPluginException {

        List<String> columnNames = getColumnNames();

        if (columnNames.isEmpty()) {
            createTable();
        } else {
            alterTable(columnNames);
        }
    }

    // Package protected to facilitate testing
    @SuppressWarnings("unchecked")
    List<String> getColumnNames() throws WeatherPluginException {

        DatabaseMetaDataCallback callback = new DatabaseMetaDataCallback() {

            public Object processMetaData(DatabaseMetaData dbmd) throws SQLException {

                String readingsTable = READINGS_TABLE;

                if (dbmd.storesUpperCaseIdentifiers()) {
                    readingsTable = readingsTable.toUpperCase();
                }

                ResultSet rs = dbmd.getColumns(null, null, readingsTable, "%");
                ArrayList<String> names = new ArrayList<String>();
                while (rs.next()) {
                    names.add(rs.getString(4).toLowerCase());
                }
                return names;
            }

        };

        List<String> columnNames;
        try {
            columnNames = (List<String>) JdbcUtils.extractDatabaseMetaData(dataSource, callback);
        } catch (MetaDataAccessException e) {
            throw new WeatherPluginException("Can't extract metadata from database", e);
        }
        return columnNames;
    }

    private void createTable() {

        String createTable = String.format("CREATE TABLE %s (%s DATETIME, %s BOOLEAN", READINGS_TABLE, READING_TIME_COLUMN,
                CURRENT_COLUMN);
        StringBuilder sb = new StringBuilder(createTable);

        for (String columnName : COLUMN_NAMES) {
            sb.append(", ");
            sb.append(columnName);
            sb.append(" REAL DEFAULT NULL");
        }
        sb.append(")");
        String sql = sb.toString();
        LOG.debug(sql);

        jdbc.execute(sql);
        LOG.info("Created table " + READINGS_TABLE);

    }

    private void alterTable(List<String> columnNames) {

        ArrayList<String> addedColumns = new ArrayList<String>();
        LOG.debug("Table exists - checking for new columns");
        for (String columnName : COLUMN_NAMES) {
            if (!columnNames.contains(columnName)) {
                addedColumns.add(columnName);
            }
        }

        for (String columnName : addedColumns) {
            String sql = "ALTER TABLE " + READINGS_TABLE + " ADD COLUMN " + columnName + " REAL DEFAULT NULL";
            jdbc.execute(sql);
            LOG.debug(sql);
        }

        if (!addedColumns.isEmpty()) {
            LOG.info("Added columns " + addedColumns + " to table " + READINGS_TABLE);
        }
    }

}
